/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.LocationDTO;
import east2west.dto.SchedulesDTO;
import east2west.interfaces.SchedulesInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author nguyendat
 */
public class SchedulesDAO implements SchedulesInterface {

    Connection con = null;
    CallableStatement call = null;
    SchedulesDTO dto = null;
    ResultSet rs = null;
    LocationDTO localDTO = null;
    LocationDAO localDAO = null;

    public ArrayList<SchedulesDTO> readByAll() {
        ArrayList<SchedulesDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Schedules_GetAll}");
            rs = call.executeQuery();
            list = new ArrayList<SchedulesDTO>();
            while (rs.next()) {
                dto = new SchedulesDTO();
                dto.setSchedulesId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setLocationId(rs.getInt(3));
                dto.setDescription(rs.getString(4));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }
    }

    public SchedulesDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Schedules_FindId(?)}");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new SchedulesDTO();
                dto.setSchedulesId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setLocationId(rs.getInt(3));
                dto.setDescription(rs.getString(4));
            }
            rs.close();
            call.close();
            return dto;
        } catch (SQLException ex) {

            return null;
        }

    }

    public ArrayList<SchedulesDTO> findByTourId(int id) {
        ArrayList<SchedulesDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_Schedules where TourId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            list = new ArrayList<SchedulesDTO>();
            while (rs.next()) {
                dto = new SchedulesDTO();
                dto.setSchedulesId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                localDTO = new LocationDTO();
                localDTO.setLocationId(rs.getInt(3));
                localDTO.setLocationName(rs.getString(4));
                dto.setLocationDTO(localDTO);
                dto.setDescription(rs.getString(5));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }


    }

    public ArrayList<SchedulesDTO> findByLocationId(int id) {
        ArrayList<SchedulesDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Schedules_LocationId(?)}");
            call.setInt(1, id);

            rs = call.executeQuery();
            list = new ArrayList<SchedulesDTO>();
            while (rs.next()) {
                dto = new SchedulesDTO();
                dto.setSchedulesId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setLocationId(rs.getInt(3));
                dto.setDescription(rs.getString(4));

                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }

    }

    public ArrayList<SchedulesDTO> findByDTO(SchedulesDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean create(SchedulesDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Insert_Schedules(?,?,?)}");
            call.setInt(1, dto.getTourId());
            call.setInt(2, dto.getLocationId());
            call.setString(3, dto.getDescription());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(SchedulesDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Update_Schedules(?,?,?)}");
            call.setInt(1, dto.getSchedulesId());
            call.setInt(2, dto.getLocationId());
            call.setString(3, dto.getDescription());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Delete_Schedules(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(SchedulesDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public ArrayList<LocationDTO> findSchedulesByTourId(int id) {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT *FROM V_Schedules WHERE TourId=?");
            call.setInt(1, id);

            rs = call.executeQuery();
            ArrayList<LocationDTO> list = new ArrayList<LocationDTO>();
            while (rs.next()) {
                LocationDTO ldto = new LocationDTO();
                dto = new SchedulesDTO();
                dto.setSchedulesId(rs.getInt(1));
                ldto.setSchedulesDTO(dto);
                ldto.setLocationId(rs.getInt(2));
                ldto.setDescription(rs.getString(3));
                ldto.setLocationName(rs.getString(4));
                list.add(ldto);
            }

            return list;
        } catch (SQLException ex) {

            return null;
        }

    }
}
